from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, update, delete

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:Admin!23@172.16.254.38:3306/db1'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_recycle': 280,
    'pool_timeout': 20,
    'pool_pre_ping': True
}

db = SQLAlchemy(app)


# ===== 模型定义 =====
class Department(db.Model):
    __tablename__ = 'departments'
    department_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    department_name = db.Column(db.String(100), unique=True, nullable=False)
    employees = db.relationship("Employee", back_populates="department")


class Employee(db.Model):
    __tablename__ = 'employees'
    employee_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(100), unique=True)
    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))
    department = db.relationship("Department", back_populates="employees")


# ===== 创建数据库 =====
with app.app_context():
    db.create_all()


# ===== 部门增（Create Department） =====
@app.route("/departments", methods=["POST"])
def create_department():
    data = request.json
    new_dept = Department(department_name=data["department_name"])
    db.session.add(new_dept)
    db.session.commit()
    return jsonify({"message": "Department created", "id": new_dept.department_id}), 201


# ===== 员工增（Create Employee） =====
@app.route("/employees", methods=["POST"])
def create_employee():
    data = request.json
    # 如果部门不存在，可以先创建部门
    dept_name = data.get("department_name")
    dept = None
    if dept_name:
        dept = db.session.scalar(select(Department).where(Department.department_name == dept_name))
        if not dept:
            dept = Department(department_name=dept_name)
            db.session.add(dept)
            db.session.flush()  # 获取 dept.department_id 不用 commit
    new_emp = Employee(
        first_name=data["first_name"],
        last_name=data["last_name"],
        email=data.get("email"),
        department_id=dept.department_id if dept else data.get("department_id")
    )
    db.session.add(new_emp)
    db.session.commit()
    return jsonify({"message": "Employee created", "id": new_emp.employee_id}), 201


# ===== 查询员工（Read Employee + Department） =====
@app.route("/employees", methods=["GET"])
def get_employees():
    stmt = select(
        Employee.employee_id,
        Employee.first_name,
        Employee.last_name,
        Employee.email,
        Department.department_name
    ).outerjoin(Department.employees).where(Department.department_id > 2)

    results = db.session.execute(stmt).all()
    data = [
        {
            "employee_id": emp_id,
            "first_name": fn,
            "last_name": ln,
            "email": email,
            "department": dept_name
        }
        for emp_id, fn, ln, email, dept_name in results
    ]
    return jsonify(data)


# ===== 修改员工 =====
@app.route("/employees/<int:emp_id>", methods=["PUT"])
def update_employee(emp_id):
    data = request.json
    # 允许修改部门名，如果部门不存在就新建
    dept_name = data.pop("department_name", None)
    if dept_name:
        dept = db.session.scalar(select(Department).where(Department.department_name == dept_name))
        if not dept:
            dept = Department(department_name=dept_name)
            db.session.add(dept)
            db.session.flush()
        data["department_id"] = dept.department_id

    stmt = update(Employee).where(Employee.employee_id == emp_id).values(**data)
    db.session.execute(stmt)
    db.session.commit()
    return jsonify({"message": "Employee updated"})


# ===== 删除员工 =====
@app.route("/employees/<int:emp_id>", methods=["DELETE"])
def delete_employee(emp_id):
    stmt = delete(Employee).where(Employee.employee_id == emp_id)
    db.session.execute(stmt)
    db.session.commit()
    return jsonify({"message": "Employee deleted"})


# ===== 查询部门及员工 =====
@app.route("/departments", methods=["GET"])
def get_departments():
    stmt = select(
        Department.department_id,
        Department.department_name,
        Employee.employee_id,
        Employee.first_name,
        Employee.last_name
    ).join(Employee, Employee.department_id == Department.department_id, isouter=True)

    results = db.session.execute(stmt).all()
    # 整理成部门 -> 员工列表
    dept_dict = {}
    for dept_id, dept_name, emp_id, first_name, last_name in results:
        if dept_id not in dept_dict:
            dept_dict[dept_id] = {"department_id": dept_id, "department_name": dept_name, "employees": []}
        if emp_id:
            dept_dict[dept_id]["employees"].append({
                "employee_id": emp_id,
                "first_name": first_name,
                "last_name": last_name
            })
    return jsonify(list(dept_dict.values()))

#=============================test

@app.route("/test1")
def test1():
    stmt = select(Department.department_id,Department.department_name)
    result = db.session.execute(stmt)
    department_dict = {id: name for id, name in result}
    return jsonify(department_dict)